﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BookManagermentSystem
{
    public partial class BorrAndRetForm : Form
    {
        string connStr = "Server=127.0.0.1;Database=csharpbookdb;uid=root;pwd=123456;";

        public BorrAndRetForm()
        {
            InitializeComponent();
        }

        private void btnBackMenu_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void BorrAndRetForm_Load(object sender, EventArgs e)
        {
            DataBind();
        }

        private void BorrAndRetForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            MainMenuForm.getInstance().Show();
        }

        private void DataBind()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "select rdID 读者编号, bkID 书号, DateBorrow 借书日期, DateLendPlan 应还日期 from borrow";
                    MySqlDataAdapter sda = new MySqlDataAdapter(comm);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    dgvBorrow.DataSource = ds.Tables[0];
                    tbBkID.DataBindings.Clear();
                    tbRdID.DataBindings.Clear();

                    tbBkID.DataBindings.Add("Text", ds.Tables[0], "书号");
                    tbRdID.DataBindings.Add("Text", ds.Tables[0], "读者编号");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作数据库出错 " + ex.Message);
            }
        }

        private void btnBorrBook_Click(object sender, EventArgs e)
        {
            try
            {
                usp_BorrowBook(tbBkID.Text, tbRdID.Text);
                MessageBox.Show("借书成功");
                DataBind();
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作数据库出错 " + ex.Message);
            }
        }

        //借书存储过程转换
        private void usp_BorrowBook(string bookID, string rdID)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                MySqlCommand comm = conn.CreateCommand();
                comm.CommandText = "select count(1) from book where bkID = @bkID";
                comm.Parameters.AddWithValue("@bkId", bookID);
                MySqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    if (dr.GetInt32(0) == 0)
                        throw new Exception("没有该书");
                }

                dr.Close();

                comm.CommandText = "select count(1) from reader where rdID = @rdID";
                comm.Parameters.AddWithValue("@rdID", rdID);
                MySqlDataReader dr1 = comm.ExecuteReader();

                if (dr1.Read())
                {
                    if (dr1.GetInt32(0) == 0)
                        throw new Exception("没有该读者");
                }

                dr1.Close();

                comm.CommandText = "select bkStatus from book where bkID = @bkID1";
                comm.Parameters.AddWithValue("@bkID1", bookID);
                MySqlDataReader dr2 = comm.ExecuteReader();

                if (dr2.Read())
                {
                    if (dr2.GetInt32(0) == 0)
                    {
                        throw new Exception("书不在馆");
                    }
                }

                dr2.Close();

                comm.CommandText = "select rdBorrowQty from reader where rdID = @rdID1";
                comm.Parameters.AddWithValue("@rdID1", rdID);
                MySqlDataReader rdBorrowQty = comm.ExecuteReader();

                rdBorrowQty.Read();
                int rdBorrowQtyC = rdBorrowQty.GetInt32(0);
                rdBorrowQty.Close();

                comm.CommandText = "select canLendQty from readertype where rdType = (select rdType from reader where rdID = @rdID2)";
                comm.Parameters.AddWithValue("@rdID2", rdID);
                MySqlDataReader canLendQty = comm.ExecuteReader();

                canLendQty.Read();
                int canLendQtyC = canLendQty.GetInt32(0);
                canLendQty.Close();

                if (rdBorrowQtyC == canLendQtyC)
                {
                    throw new Exception("达到最大借书量");
                }

                //借书
                comm.CommandText = "select canLendDay from readertype where rdType = (select rdType from reader where rdID = @rdID5)";
                comm.Parameters.AddWithValue("@rdID5", rdID);
                MySqlDataReader canLendDay = comm.ExecuteReader();

                canLendDay.Read();
                int canLendDayC = canLendDay.GetInt32(0);
                canLendDay.Close();

                comm.CommandText = "update book set bkStatus = 0 where bkID = @bkID2";
                comm.Parameters.AddWithValue("@bkID2", bookID);
                comm.ExecuteNonQuery();

                comm.CommandText = "update reader set rdBorrowQty = rdBorrowQty + 1 where rdID = @rdID3";
                comm.Parameters.AddWithValue("@rdID3", rdID);
                comm.ExecuteNonQuery();

                comm.CommandText = "insert into borrow values(@rdID4, @bkID4, now(), DATE_ADD(NOW(), INTERVAL @canLendDay DAY))";
                comm.Parameters.AddWithValue("@rdID4", rdID);
                comm.Parameters.AddWithValue("@bkID4", bookID);
                comm.Parameters.AddWithValue("@canLendDay", canLendDayC);
                comm.ExecuteNonQuery();
            }
        }

        private void btnRetBook_Click(object sender, EventArgs e)
        {
            try
            {
                ups_returnBook(tbBkID.Text, tbRdID.Text);
                MessageBox.Show("还书成功");
                DataBind();
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作数据库出错 " + ex.Message);
            }
        }

        private void ups_returnBook(string bookID, string rdID)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                MySqlCommand comm = conn.CreateCommand();
                comm.CommandText = "select count(1) from borrow where bkID = @bkID and rdID = @rdID";
                comm.Parameters.AddWithValue("@bkId", bookID);
                comm.Parameters.AddWithValue("@rdID", rdID);
                MySqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    if (dr.GetInt32(0) == 0)
                        throw new Exception("未借该书");
                }

                dr.Close();

                //还书
                comm.CommandText = "update book set bkStatus = 1 where bkID = @bkID1";
                comm.Parameters.AddWithValue("@bkId1", bookID);
                comm.ExecuteNonQuery();

                comm.CommandText = "update reader set rdBorrowQty = rdBorrowQty - 1 where rdID = @rdID1";
                comm.Parameters.AddWithValue("@rdID1", rdID);
                comm.ExecuteNonQuery();

                comm.CommandText = "delete from borrow where rdID = @rdID2 and bkID = @bkID2";
                comm.Parameters.AddWithValue("@rdID2", rdID);
                comm.Parameters.AddWithValue("@bkID2", bookID);
                comm.ExecuteNonQuery();

            }
        }
    }
}
